-- @owner: @peilinqian
-- @date: 2024/10/22
-- @testpoint: after--update在表上支持行级触发器，使用show triggers查询

@conn PrimaryDbBmode;

--step1：创建表 expect：成功
create table t_compatibility_grammar_show_case0009(id1 int, id2 int, id3 int);
insert into t_compatibility_grammar_show_case0009 values(100,200,300);
create table t_compatibility_grammar_show_case0009_1(id1 int, id2 int, id3 int);
insert into t_compatibility_grammar_show_case0009_1 values(1,2,3);


--step2：创建触发器函数 expect：成功
create or replace function f_compatibility_grammar_show_case0009() returns trigger as
$$
declare
begin
 update t_compatibility_grammar_show_case0009_1 set id3 = 5 where id1=1;
 return new;
end
$$ language plpgsql;
/

--step3：创建创建触发器 expect：成功
create trigger trigger_compatibility_grammar_show_case0009 after update on t_compatibility_grammar_show_case0009 for each row execute procedure f_compatibility_grammar_show_case0009();
/

--step4：执行update触发事件 expect：成功
update t_compatibility_grammar_show_case0009 set id3=400 where id1=100;
select * from t_compatibility_grammar_show_case0009;
--检查触发结果
select * from t_compatibility_grammar_show_case0009_1;

--step5：查询触发器信息不加可选项 expect：显示结果正确
show triggers ;

--step6：查询触发器信息from schema； expect：显示结果正确
show triggers from public;

--step7：查询触发器信息添加where条件； expect：显示结果正确
--查询触发器，添加where条件
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0009';
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0009' and "Event" ='UPDATE';
--查询触发器，添加like条件
show triggers from public where "Trigger" like '%grammar_show_case0009';

--step8：清理数据； expect：显示结果正确
drop trigger trigger_compatibility_grammar_show_case0009 on t_compatibility_grammar_show_case0009;
drop function f_compatibility_grammar_show_case0009();
drop table t_compatibility_grammar_show_case0009;
drop table t_compatibility_grammar_show_case0009_1;
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0009';